package com.javaweb.demo.dao;

import com.javaweb.demo.entity.SFile;
import com.javaweb.demo.entity.User;
import com.javaweb.demo.entity.Poster;
import com.javaweb.demo.entity.grade;
import com.javaweb.demo.entity.message;
import com.javaweb.demo.util.DBconn;
import org.apache.commons.io.IOUtils;

import java.io.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaolmpl implements UserDao {
    @Override
    public boolean login(String name, String pwd) {
        boolean flag = false;
        try {
            DBconn.init();
            ResultSet rs = DBconn.selectSql("select * from user where name='" + name + "' and pwd='" + pwd + "'");
            while (rs.next()) {
                if (rs.getString("name").equals(name) && rs.getString("pwd").equals(pwd)) {
                    flag = true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return flag;
    }

    @Override
    public boolean register(User user) {
        try {
            DBconn.init();
            ResultSet rs = DBconn.selectSql("select * from user where name='" + user.getName() + "'");
            if (rs.next()) {
                DBconn.closeConn();
                return false;
            } else {
                int i = DBconn.addUpdDel("insert into user(name,pwd,sex,home,info) " +
                        "values('" + user.getName() + "','" + user.getPwd() + "','" + user.getSex() + "','" + user.getHome() + "','" + user.getInfo() + "')");
                if (i > 0) {
                    DBconn.closeConn();
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DBconn.closeConn();
        return false;
    }

    @Override
    public List<User> getUserAll() {
        List<User> list = new ArrayList<>();
        try {
            DBconn.init();
            ResultSet rs = DBconn.selectSql("select * from user");
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPwd(rs.getString("pwd"));
                user.setSex(rs.getString("sex"));
                user.setHome(rs.getString("home"));
                user.setInfo(rs.getString("info"));
                list.add(user);
            }
            DBconn.closeConn();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean update(int id, String name, String pwd, String sex, String home, String info) {
        boolean flag = false;
        DBconn.init();
        String sql = "update user set name ='" + name
                + "' , pwd ='" + pwd
                + "' , sex ='" + sex
                + "' , home ='" + home
                + "' , info ='" + info + "' where id = " + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public boolean delete(String name) {
        boolean flag = false;
        DBconn.init();
        String sql = "delete from user where name = '" + name + "'";
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        sql = "delete from friend where (username = '" +  name + "') or (name = '" + name + "')";
        i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public List<User> selectUser(int id,String name,String sex,String home) {
        List<User> list = new ArrayList<>();
        try {
            DBconn.init();
            String sql = "select * from user where (name = '" + name + "') or (name = '" + name + "' and sex = '" + sex + "' and home = '" + home + "' ) or id =" + id;
            ResultSet rs = DBconn.selectSql(sql);
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setSex(rs.getString("sex"));
                user.setHome(rs.getString("home"));
                user.setInfo(rs.getString("info"));
                list.add(user);
            }
            DBconn.closeConn();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean addfriend(String username,String name,String request) {
        try {
            DBconn.init();
            ResultSet rs = DBconn.selectSql("select * from friend where username='" + username +
                    "' and name='" + name + "'");
            if (rs.next()) {
                DBconn.closeConn();
                return false;
            } else {
                int i = DBconn.addUpdDel("insert into addfriendrequest(username,name,request)" +
                        "values('" + username + "','" + name + "','" + request + "')");
                if (i > 0) {
                    DBconn.closeConn();
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DBconn.closeConn();
        return false;
    }

    @Override
    public boolean addfriend(String username,String name) {
        try {
            DBconn.init();
            ResultSet rs = DBconn.selectSql("select * from friend where username='" + username +
                    "' and name='" + name + "'");
            if (rs.next()) {
                DBconn.closeConn();
                return false;
            } else {
                int i = DBconn.addUpdDel("insert into friend(username,name) " +
                        "values('" + username + "','" + name + "')");
                if (i > 0) {
                    i = DBconn.addUpdDel("insert into friend(name,username) " +
                            "values('" + username + "','" + name + "')");
                    if (i > 0) {
                        DBconn.closeConn();
                        return true;
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        DBconn.closeConn();
        return false;
    }

    @Override
    public boolean delfriend(String username,String name) {
        boolean flag = false;
        DBconn.init();
        String sql = "delete from friend where (username = '" + username + "') and (name = '" + name + "')";
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public boolean deleterequest(String name,String request) {
        boolean flag = false;
        DBconn.init();
        String sql = "delete from addfriendrequest where (request = '" + request + "') and (name = '" + name + "')";
        int i = DBconn.addUpdDel(sql);
        System.out.println(i);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public List<User> queryfriends(String username) {
        List<User> list = new ArrayList<>();
        try {
            DBconn.init();
            String sql = "select name from friend where (username = '" + username + "')";
            ResultSet rs = DBconn.selectSql(sql);
            while (rs.next()) {
                List<User> friend = selectUser(0,rs.getString("name"),null,null);
                list.add(friend.get(0));
            }
            DBconn.closeConn();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> queryfriendrequests(String username) {
        List<User> list = new ArrayList<>();
        try {
            DBconn.init();
            String sql = "select username,request from addfriendrequest where (name = '" + username + "')";
            ResultSet rs = DBconn.selectSql(sql);
            while (rs.next()) {
                List<User> friend = selectUser(0,rs.getString("username"),null,null);
                if (!friend.isEmpty()) {
                    friend.get(0).setAddfriendrequest(rs.getString("request"));
                    list.add(friend.get(0));
                }
            }
            DBconn.closeConn();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean setposter(String str) {
        DBconn.init();
        int i = DBconn.addUpdDel("insert into poster(str) " +
                "values('" + str + "')");
        if (i > 0) {
            DBconn.closeConn();
            return true;
        }
        DBconn.closeConn();
        return false;
    }

    @Override
    public boolean updateposter(int id,String str) {
        boolean flag = false;
        DBconn.init();
        String sql = "update poster set str ='" + str
                + "' where id = " + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public List<Poster> getposter() {
        List<Poster> list = new ArrayList<>();
        try {
            DBconn.init();
            ResultSet rs = DBconn.selectSql("select * from poster");
            while (rs.next()) {
                Poster poster = new Poster();
                poster.setId(rs.getInt("id"));
                poster.setStr(rs.getString("str"));
                list.add(poster);
            }
            DBconn.closeConn();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean deleteposter(int id) {
        boolean flag = false;
        DBconn.init();
        String sql = "delete from poster where id = " + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public boolean upload(InputStream path, String filename) throws FileNotFoundException {
        boolean flag = false;
        try {
            DBconn.init();
            String sql = "insert into tab_bin(filename,data) values('"+ filename + "','" + path + "')";
            int i = DBconn.addUpdDel(sql);
            System.out.println(i);
            if (i > 0) {
                flag = true;
            }
            DBconn.closeConn();
            return flag;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean download(int id) {
        boolean flag = false;
        try {
            DBconn.init();
            String sql = "select filename,data from tab_bin where id="+ id;
            ResultSet rs = DBconn.selectSql(sql);
            while (rs.next()) {
                String filename = rs.getString("filename");
                OutputStream out = new FileOutputStream("/home/bran/Downloads/" + filename);
                InputStream in = rs.getBinaryStream("data");
                IOUtils.copy(in,out);
                out.close();
                flag = true;
            }
            DBconn.closeConn();
            return flag;
        } catch (SQLException | FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public List<SFile> getFileAll() {
        List<SFile> list = new ArrayList<>();
        try {
            DBconn.init();
            ResultSet rs = DBconn.selectSql("select id,filename from tab_bin");
            while (rs.next()) {
                SFile Sfile = new SFile();
                Sfile.setId(rs.getInt("id"));
                System.out.println("id="+rs.getInt("id"));
                Sfile.setName(rs.getString("filename"));
                list.add(Sfile);
            }
            DBconn.closeConn();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<grade> querygrades(String username) {
        List<grade> grades = new ArrayList<>();
        try {
            DBconn.init();
            String sql = "select * from grade where (studentname = '" + username + "')";
            ResultSet rs = DBconn.selectSql(sql);
            while (rs.next()) {
                grade grade = new grade();
                grade.setId(rs.getInt("id"));
                grade.setClassname(rs.getString("classname"));
                grade.setStudentname(rs.getString("studentname"));
                grade.setGrade(rs.getString("grade"));
                grades.add(grade);
            }
            DBconn.closeConn();
            return grades;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    @Override
    public List<grade> getallgrades() {
        List<grade> grades = new ArrayList<>();
        try {
            DBconn.init();
            ResultSet rs = DBconn.selectSql("select * from grade");
            while (rs.next()) {
                grade grade = new grade();
                grade.setId(rs.getInt("id"));
                grade.setClassname(rs.getString("classname"));
                grade.setStudentname(rs.getString("studentname"));
                grade.setGrade(rs.getString("grade"));
                grades.add(grade);
            }
            DBconn.closeConn();
            return grades;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    @Override
    public boolean setgrade(String sname,String cname,String grade) {
        DBconn.init();
        int i = DBconn.addUpdDel("insert into grade(studentname,classname,grade) " +
                "values('" + sname + "','" + cname + "','" + grade + "')");
        if (i > 0) {
            DBconn.closeConn();
            return true;
        }
        DBconn.closeConn();
        return false;
    }
    @Override
    public boolean updategrade(int id,String sname,String cname,String grade) {
        boolean flag = false;
        DBconn.init();
        String sql = "update grade set studentname ='" + sname
                + "', classname ='" + cname +"',grade = '"+grade+"' where id = " + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }
    @Override
    public boolean deletegrade(int id) {
        boolean flag = false;
        DBconn.init();
        String sql = "delete from grade where id = " + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public List<message> getmessages(String receivername) {
        List<message> list = new ArrayList<>();
        try {
            DBconn.init();
            ResultSet rs = DBconn.selectSql("select * from message where receivername = '" + receivername + "'");
            while (rs.next()) {
                message message = new message();
                message.setId(rs.getInt("id"));
                message.setString(rs.getString("string"));
                message.setDelivername(rs.getString("delivername"));
                message.setReceivername("receivername");
                list.add(message);
            }
            DBconn.closeConn();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public boolean addmessage(String delivername,String receivername,String string) {
        DBconn.init();
        int i = DBconn.addUpdDel("insert into message(delivername,receivername,string) " +
                "values('" + delivername + "','" + receivername + "','" + string + "')");
        if (i > 0) {
            DBconn.closeConn();
            return true;
        }
        DBconn.closeConn();
        return false;
    }

}
